package com.ny.dao.impl;

import com.ny.dao.StudentDao;
import com.ny.entity.Student;
import com.ny.until.DbUntil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {

    private DbUntil dbUtil = new DbUntil();


    @Override
    public Student login(String name, String password) throws Exception {
        // 1. 连接数据库
        Connection connection = dbUtil.getCon();
        // 2. 编写SQL语句
        String sql = "select * from students where name =? and password =?";
        // 3. 执行SQL语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, name);
        preparedStatement.setString(2, password);
        // 4. 得到SQL查询的结果
        ResultSet resultSet = preparedStatement.executeQuery();
        // 5. 把结果封装成Student
        Student student = new Student();
        if (resultSet.next()) {
            // 通过列名获取到对应列的值，比如resultSet.getInt("student_id");就是对应的学生的id
            int id = resultSet.getInt("student_id");
            String sname = resultSet.getString("name");
            String phone = resultSet.getString("phone");
            String sPassword = resultSet.getString("password");
            // 获取到的数据封装成Student对象
            student.setStudentId(id);
            student.setName(sname);
            student.setPhone(phone);
            student.setPassword(sPassword);
            return student;
        }
        return null;
    }
    @Override
    public int insertStudent(Student student) throws SQLException, ClassNotFoundException {
        Connection connection = dbUtil.getCon();
        String sql = "insert into students(name,phone,password) values(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 正确调用非静态方法，使用preparedStatement实例
        preparedStatement.setString(1, student.getName());
        preparedStatement.setString(2, student.getPhone());
        preparedStatement.setString(3, student.getPassword());

        int i = preparedStatement.executeUpdate(); // 添加 删除 修改都是一样的写法
        return i;
    }

    @Override
    public int updateStudent(Student student) throws SQLException, ClassNotFoundException {

        Connection connection = dbUtil.getCon();
        String sql = "update students set name = ?,phone = ?,password = ? where student_id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, student.getName());
        preparedStatement.setString(2, student.getPhone());
        preparedStatement.setString(3, student.getPassword());
        preparedStatement.setInt(4, student.getStudentID());
        return preparedStatement.executeUpdate();

    }

    @Override
    public List<Student> findAllStudents() throws Exception, ClassNotFoundException {
        Connection  connection = dbUtil.getCon();
        String sql = "select * from students";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<Student> studentList = new ArrayList<>();
        while (resultSet.next()) {
            Student student = new Student();
            student.setStudentId(resultSet.getInt("student_id"));
            student.setName(resultSet.getString("name"));
            student.setPhone(resultSet.getString("phone"));
            student.setPassword(resultSet.getString("password"));
            studentList.add(student);
        }
        return studentList;
    }

    @Override
    public Student getStudentById(int id) throws Exception {
        Connection connection = dbUtil.getCon();
        String sql = "select * from students where student_id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            Student student = new Student();
            student.setStudentId(resultSet.getInt("student_id"));
            student.setName(resultSet.getString("name"));
            student.setPhone(resultSet.getString("phone"));
            student.setPassword(resultSet.getString("password"));
            return student;
        }
        return null;
    }

    @Override
    public int deleteStudent(int id) throws Exception {
        Connection connection = dbUtil.getCon();
        String sql = "delete from students where student_id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        return preparedStatement.executeUpdate();
    }

}